import java.io.File;
import javax.swing.JOptionPane;
import java.sql.*;
import java.util.Iterator;
import java.util.Vector;

/**
 * @class sqlLink
 * @description SQLite Linking driver class
 * @author CLLM
 * @version 0.1
 */
public class sqlLink {

	private Connection conn;

	private Vector<StructMemberInfo> dataMemberInfo = new Vector<StructMemberInfo>();
	private Vector<StructAttendanceInfo> tempAttendanceInfo = new Vector<StructAttendanceInfo>();
	private Vector<StructEventInfo> tempEventInfo = new Vector<StructEventInfo>();
	private Vector<StructCourseInfo> tempCourseInfo = new Vector<StructCourseInfo>();
	private String fileLocation = "";

	public sqlLink(String fileLocation) {
		try {
			this.fileLocation = fileLocation;
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + fileLocation);
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "Error Loading/Saving file",
					"ERROR", JOptionPane.ERROR_MESSAGE);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void sqlLinkNew() {
		try {
			Statement st1;
			st1 = conn.createStatement();
			st1.executeUpdate("DROP TABLE IF EXISTS memberData;");
			st1.executeUpdate("CREATE TABLE memberData (idNo INTEGER PRIMARY KEY, lName TEXT, fName TEXT, mName TEXT, bDay TEXT, eAdd TEXT, contactNo TEXT, course TEXT, memberType INTEGER, description TEXT);");
			st1.executeUpdate("DROP TABLE IF EXISTS courseData;");
			st1.executeUpdate("CREATE TABLE courseData (classId INTEGER PRIMARY KEY AUTOINCREMENT, courseCode TEXT, section TEXT, professor TEXT, college TEXT, department TEXT);");
			st1.executeUpdate("DROP TABLE IF EXISTS events;");
			st1.executeUpdate("CREATE TABLE events (idEvent INTEGER PRIMARY KEY AUTOINCREMENT, eventName TEXT, eventDate TEXT);");
			st1.executeUpdate("DROP TABLE IF EXISTS attendance;");
			st1.executeUpdate("CREATE TABLE attendance (attendanceId INTEGER PRIMARY KEY AUTOINCREMENT, idNo INTEGER, idEvent INTEGER);");

			// TEST:
			// PreparedStatement prep =
			// conn.prepareStatement("INSERT INTO memberData VALUES (?, ?, ?,?, ?, ?, ?,?);");
			// System.out.println("Start 1");
			// prep.setInt(1, 1111111);
			// prep.setString(2, "sampleL");
			// prep.setString(3, "sampleF");
			// prep.setString(4, "sampleM");
			// prep.setString(5, "20120623);
			// prep.setString(6, "sample@db.com");
			// prep.setString(7, "09111111111");
			// prep.setString(8, "AB-ADV");
			// prep.setInt(9, 0);
			// prep.setString(10, "");
			// prep.executeUpdate();

		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "ERROR SQL", "ERROR",
					JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}
	}

	public void sqlAddMemberAll(Vector<StructMemberInfo> dataMemberInfoLink)
			throws SQLException {
		PreparedStatement prep = conn
				.prepareStatement("INSERT INTO memberData VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

		conn.setAutoCommit(false);
		for (StructMemberInfo memberInfoTemp : dataMemberInfoLink) {
			prep.setInt(1, memberInfoTemp.idNo);
			prep.setString(2, memberInfoTemp.getLastName());
			prep.setString(3, memberInfoTemp.getFirstName());
			prep.setString(4, memberInfoTemp.getMiddleName());
			prep.setString(5, memberInfoTemp.getBirthdayYYYYMMDD());
			prep.setString(6, memberInfoTemp.getEmailAdd());
			prep.setString(7, memberInfoTemp.getContactNo());
			prep.setString(8, memberInfoTemp.getCourse());
			prep.setInt(9, memberInfoTemp.getMemberTypeNo());
			prep.setString(10, memberInfoTemp.getDescription());
			prep.addBatch();
		}
		prep.executeBatch();
		conn.setAutoCommit(true);
	}

	public Vector<StructMemberInfo> sqlRetrieveMemberAll() {
		Statement st2;
		Vector<StructMemberInfo> tempMemberInfo = new Vector<StructMemberInfo>();
		try {
			st2 = conn.createStatement();
			ResultSet rs = st2.executeQuery("SELECT * from memberData;");
			retrieveMember(rs);
			return tempMemberInfo;
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "Error reading database",
					"ERROR", JOptionPane.ERROR_MESSAGE);
			return null;
		}
	}

	public StructMemberInfo sqlRetrieveMemberviaID(int id) {
		StructMemberInfo tempMemberInfo = new StructMemberInfo();
		Statement st2;
		try {
			st2 = conn.createStatement();
			ResultSet rs = st2
					.executeQuery("SELECT * FROM memberData WHERE idNo LIKE '%"
							+ String.valueOf(id) + "%';");
			retrieveMember(rs);
			return tempMemberInfo;
		} catch (SQLException ex) {
			 JOptionPane.showMessageDialog(null, "Error reading database", "ERROR", JOptionPane.ERROR_MESSAGE);
			return null;
		}
	}

	private StructMemberInfo retrieveMember(ResultSet rsPass) {
		StructMemberInfo tempMemberInfo = new StructMemberInfo();
		try{
			dataMemberInfo.clear();
			while (rsPass.next()) {
				dataMemberInfo.add(new StructMemberInfo(rsPass.getInt("idNo"), rsPass.getString("fName"), rsPass.getString("mName"), rsPass.getString("lName"), rsPass.getString("eAdd"), 
						rsPass.getString("contactNo"), rsPass.getString("coruse"), rsPass.getString("bDay"), rsPass.getInt("memberType"), rsPass.getString("description")));
			}
			rsPass.close();
			return tempMemberInfo;
		}
		catch (SQLException ex){
			JOptionPane.showMessageDialog(null, "Error reading database", "ERROR", JOptionPane.ERROR_MESSAGE);
			return null;
		}
	}

	public void sqlAddMember(StructMemberInfo callInfo) throws SQLException {
		PreparedStatement prep = conn
				.prepareStatement("INSERT INTO memberData VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
		prep.setInt(1, callInfo.idNo);
		prep.setString(2, callInfo.getLastName());
		prep.setString(3, callInfo.getFirstName());
		prep.setString(4, callInfo.getMiddleName());
		prep.setString(5, callInfo.getBirthdayYYYYMMDD());
		prep.setString(6, callInfo.getEmailAdd());
		prep.setString(7, callInfo.getContactNo());
		prep.setString(8, callInfo.getCourse());
		prep.setInt(9, callInfo.getMemberTypeNo());
		prep.setString(10, callInfo.getDescription());
		prep.executeUpdate();
	}

	public Vector<StructAttendanceInfo> sqlretrieveAttendance() {
		Statement st2;
		try {
			st2 = conn.createStatement();
			tempAttendanceInfo.clear();
			ResultSet rs = st2.executeQuery("SELECT * from attendance, events WHERE attendance.idEvent = events.idEvent;");
			while (rs.next()) {
				tempAttendanceInfo.add(new StructAttendanceInfo(rs.getInt("attendancId"), rs.getInt("idNo"), rs.getInt("idEvent"), rs.getString("eventName"), rs.getString("eventDate")));
			}

			rs.close();
			return tempAttendanceInfo;
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "Error reading database", "ERROR", JOptionPane.ERROR_MESSAGE);
			return null;
		}
	}

	public Vector<StructEventInfo> sqlretrieveEvent(ResultSet rsPass) {
		Statement st2;
		try {
			st2 = conn.createStatement();
			tempEventInfo.clear();
			ResultSet rs = st2
					.executeQuery("SELECT * from events;");
			while (rs.next()) {
				tempEventInfo.add(new StructEventInfo(rs.getInt("idEvent"), rs.getString("eventName"), rs.getString("eventDate"))); 
			}	//(idEvent INTEGER PRIMARY KEY, eventName TEXT, eventDate TEXT)
			rs.close();
			return tempEventInfo;
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "Error reading database", "ERROR", JOptionPane.ERROR_MESSAGE);
			return null;
		}
	}

	public Vector<StructCourseInfo> sqlretrieveCourse(ResultSet rsPass) {
		Statement st2;
		try {
			st2 = conn.createStatement();
			tempCourseInfo.clear();
			ResultSet rs = st2.executeQuery("SELECT * from courseData;");
			while (rs.next()) {
				tempCourseInfo.add(new StructCourseInfo(rs.getInt("classId"), rs.getString("courseCode"), rs.getString("section"), rs.getString("professor"), rs.getString("department"), rs.getString("college")));
			}
			rs.close();
			return tempCourseInfo;
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "Error reading database", "ERROR", JOptionPane.ERROR_MESSAGE);
			return null;
		}
	}

	//attendance (id INTEGER PRIMARY KEY AUTOINCREMENT, idNo INTEGER, idEvent INTEGER)
	public void sqlAddAttendance(StructAttendanceInfo atTemp) {
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO attendance VALUES (?, ?, ?);");
			prep.setInt(1, atTemp.getAttendanceID());
			prep.setInt(2, atTemp.getIdNo());
			prep.setInt(3, atTemp.getEventID());
			prep.executeUpdate();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "Error Adding", "Error", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}
	}

	//events (idEvent INTEGER PRIMARY KEY AUTOINCREMENT, eventName TEXT, eventDate TEXT);
	public void sqlAddEvent(StructEventInfo eTemp) {
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO events VALUES (?, ?, ?);");
			prep.setInt(1, eTemp.getEventID());
			prep.setString(2, eTemp.getEventName());
			prep.setString(3, eTemp.getEventDateYYYYMMDD());
			prep.executeUpdate();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "Error Adding", "Error", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}
	}

	//courseData (classId INTEGER PRIMARY KEY AUTOINCREMENT, courseCode TEXT, section TEXT, professor TEXT, college TEXT, department TEXT);
	public void sqlAddCourse(StructCourseInfo cTemp) {
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement("INSERT INTO events VALUES (?, ?, ?, ?, ?, ?);");
			prep.setInt(1, cTemp.getClassID());
			prep.setString(2, cTemp.getCourseCode());
			prep.setString(3, cTemp.getSection());
			prep.setString(4, cTemp.getProfessorName()); 
			prep.setString(5, cTemp.getProfessorCollege()); 
			prep.setString(6, cTemp.getProfessorDepartment()); 
			prep.executeUpdate();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "Error Adding", "Error", JOptionPane.ERROR_MESSAGE);
			e.printStackTrace();
		}
	}

	public void sqlClose() throws SQLException {
		conn.close();
	}
}
